package com.vogue.supplymall.shop.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.domain.dto.SearchInfo;
import com.vogue.supplymall.shop.common.OrderUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.Arrays;

/**
 * The type Shop sql provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-01-02
 */
public class ShopSqlProvider extends BaseSqlProvider {

    /**
     * Find string.
     *
     * @param searchInfo the search info
     * @return the string
     */
    public String find(SearchInfo searchInfo) {
        StringBuilder sql = new StringBuilder();
        // 查询项目
        sql.append(getSelectPart());
        // 表连接
        sql.append(getFromPart());
        // 查询条件
        sql.append(getWherePart(searchInfo));
        sql.append(" GROUP BY ")
                .append(" id ");
        sql.append(OrderUtils.orderBy(searchInfo));

        // 根据前端传的开始结束位置进行分页检索
        sql.append(" LIMIT #{start},#{length}");
        
        return DealStatus.formatShadeSql(sql.toString());
    }

    /**
     * 查询总记录数
     * 
     * @param searchInfo
     * @return
     */
    public String findCount(SearchInfo searchInfo) {
    	StringBuilder sql = new StringBuilder("SELECT COUNT(*) n FROM (");
        // 查询项目
        sql.append(getSelectPart());
        // 表连接
        sql.append(getFromPart());
        // 查询条件
        sql.append(getWherePart(searchInfo));
        sql.append(" GROUP BY ")
                .append(" id ");
        sql.append(") t ");
        return DealStatus.formatShadeSql(sql.toString());
    }
    
    private String getSelectPart() {
        StringBuilder select = new StringBuilder();
        select.append(" SELECT ")
                .append(" s.id, ")
                .append(" s.`name` AS `name`, ")
                .append(" s.entname, ")
                .append(" s.sendamount, ")
                .append(" s.address, ")
                .append(" s.tel, ")
                .append(" s.linkman, ")
                .append(" s.area, ")
                .append(" s.province, ")
                .append(" s.city, ")
                .append(" s.summary, ")
                .append(" case when (osm.id is null or osm.imgurl='') then osm1.imgurl else osm.imgurl end imgurl ");
        return select.toString();
    }

    private String getFromPart() {
        StringBuilder from = new StringBuilder();
        from.append(" FROM ")
                .append(" shop s ")
                // 关联店铺分类表
                .append(" LEFT JOIN rel_shop_category rsc ON (s.syscodeid = rsc.syscodeid) ")
                // 关联基础分类表
                .append(" LEFT JOIN base_category bc ON (bc.id = rsc.categoryid) ")
                // 关联机构店铺图片表
                .append(" LEFT JOIN organ_shop_img osm ON ( ")
                .append(" s.syscodeid = osm.syscodeid ")
                // 2018/6/29 增加店铺图片
                .append(" and s.organid = osm.organid ")
                .append(" AND osm.imgtype = 0 ) ")
                //如果店铺图片没有，则默认为企业图片
                .append(" LEFT JOIN organ_shop_img osm1 ON ( ")
                .append(" s.syscodeid = osm1.syscodeid ")
                .append(" and osm1.organtype=0 ")
                .append(" AND osm1.imgtype = 0 ) ");
        return from.toString();
    }

    private String getWherePart(SearchInfo searchInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" WHERE ")
                .append(" s.disabledflag = 0 and s.stopflag = 0 ")
                .append(" AND s.hiddenflag = 0 ");
        // 按机构检索时需要匹配企业注册号
        if (null != searchInfo.getSyscodeid()) {
            sql.append(" AND s.syscodeid = #{syscodeid} ");
        }
        // 店铺名称和类别的模糊匹配
        if (StringUtils.isNotEmpty(searchInfo.getContent())) {
            sql.append(" AND ( ")
                    .append(" s.`name` LIKE CONCAT('%',#{content},'%') ")
                    .append(" OR bc.`name` LIKE CONCAT('%',#{content},'%') ")
                    .append(" ) ");
        }
        // 匹配筛选中选择的类别
        if (StringUtils.isNotEmpty(searchInfo.getCategoryCode())) {
            if (null != searchInfo.getCategorys()) {
                sql.append(" AND bc.id in ( ");
                Arrays.stream(searchInfo.getCategorys()).map(str -> str + ",").forEach(sql::append);
                sql.append(" ) ");
            } else {
                sql.append(" AND bc.id = #{categoryCode} ");
            }
        }
        // 进行区域的匹配
        if (StringUtils.isNotEmpty(searchInfo.getPosition()) && searchInfo.getAreaFlag() != 0) {
            sql.append(" AND ( ")
                    .append(" s.city = #{position} ")
                    // 暂时不需要对店铺的区域和省进行关联
//                    .append(" OR ")
//                    .append(" s.area = ")
//                    .append(searchInfo.getPosition())
//                    .append(" OR ")
//                    .append(" s.province = ")
//                    .append(searchInfo.getPosition())
                    .append(" ) ");
        }
        return sql.toString();
    }
    
}
